<?php

/**
 * 数据报表采购排行
 * User: Cloud
 * Date: 2018/5/14
 * Time: 10:55
 */
class Information extends CI_Controller
{
    public function __construct()
    {
        parent::__construct();
        $this->load->database();
        $this->load->helper('json');
        $this->load->model('customer_model');
        $this->load->library('session');
        $this->load->model('goods_model');
    }

    //数据报表-用户统计
    public function information_purchase_user()
    {
        $customer_id = $this->session->customer_id;
        if ($customer_id) {
            $parent_id = $this->customer_model->get_customer_user($customer_id);
            $order = $this->db->select('cus.id,cus.identity,cus.username,cus.cost_center_id,cen.name')
                ->where_in('cus.id', $parent_id)
                ->from('app_customer cus')
                ->join('app_cost_center cen', 'cen.id=cus.cost_center_id');
//            if (isset($_GET['admin']) && !empty($_GET['admin'])) {
//            } else {
//                $str[] = 1;
//                $data['admin'] = 1;
//            }

            if (isset($_GET['account']) && !empty($_GET['account'])) {
            } else {
                $str[] = 2;
                $data['account'] = 2;
            }

            if (isset($_GET['user']) && !empty($_GET['user'])) {
            } else {
                $str[] = 3;
                $data['user'] = 3;
            }


            if (!empty($str)) {
                $order = $order->where_in("identity", $str);
            } else {
                $order = $order->where_in("identity", array(4));
            }

            if (isset($_GET['username']) && !empty($_GET['username'])) {
                $username = $_GET['username'];
                $data['username'] = $username;
                $order = $order->like("username", $username);
            }
            if (isset($_GET['mydatepicker']) && !empty($_GET['mydatepicker'])) {
                $mydatepicker = $_GET['mydatepicker'];
                $data['mydatepicker'] = $mydatepicker;
                $data['ymd'] = $mydatepicker;
            }
            if (isset($_GET['mydatepicker2']) && !empty($_GET['mydatepicker2'])) {
                $mydatepicker2 = $_GET['mydatepicker2'];
                $data['mydatepicker2'] = $mydatepicker2;
                $data['ymd2'] = $mydatepicker2;
            }
            $orders = $order->get()->result_array();

            $data['orders'] = $orders;

            if (isset($_POST['ymd']) && !empty($_POST['ymd'])) {
                $dates1 = $_POST['ymd'];
                $data['ymd'] = $dates1;
            }

            if (isset($_POST['ymd2']) && !empty($_POST['ymd2'])) {
                $data['ymd2'] = $_POST['ymd2'];
                $date1 = $data['ymd2'] . '-01';
            } elseif (isset($data['ymd2']) && !empty($data['ymd2'])) {
                $date1 = $data['ymd2'] . '-01';
            } else {
                $data['ymd2'] = date('Y-m', time());
                $data['ymd'] = date('Y-m', strtotime("-5 month"));
                $date1 = date('Y-m', time()) . '-01';
            }

            //最近6个月
            $data['time6'] = date('Y-m', strtotime($date1 . "-5 month"));
            $data['time5'] = date('Y-m', strtotime($date1 . "-4 month"));
            $data['time4'] = date('Y-m', strtotime($date1 . "-3 month"));
            $data['time3'] = date('Y-m', strtotime($date1 . "-2 month"));
            $data['time2'] = date('Y-m', strtotime($date1 . "-1 month"));
            $data['time1'] = date('Y-m', strtotime($date1));

            if (isset($_POST['page'])) {
                if (isset($_POST['row'])) {
                    $listRows = 100000000;//导出所有
                } else {
                    $listRows = 10;
                }
                $nowPage = isset($_POST['page']) ? $_POST['page'] : 1;
                $firstRow = $listRows * ($nowPage - 1);
                $order = $this->db->select('cus.id,cus.identity,cus.username,cus.cost_center_id,cen.name')
                    ->where_in('cus.id', $parent_id)
                    ->from('app_customer cus')
                    ->join('app_cost_center cen', 'cen.id=cus.cost_center_id');
                $str = array();
//                if (isset($_POST['admin']) && !empty($_POST['admin'])) {
//                } else {
//                    $str[] = $_POST['admin'];
//                }
                if (isset($_POST['account']) && !empty($_POST['account'])) {
                } else {
                    $str[] = 2;
                }
                if (isset($_POST['user']) && !empty($_POST['user'])) {
                } else {
                    $str[] = 3;
                }

                if (isset($_POST['username']) && !empty($_POST['username'])) {
                    $username = $_POST['username'];
                    $order = $order->like("username", $username);
                }
                if (!empty($str)) {
                    $order = $order->where_in("identity", $str);
                } else {
                    $order = $order->where_in("identity", array(4));
                }
                $order->limit($listRows, $firstRow);
                $order = $order->get()->result_array();

                //根据用户选择的月份得到数据
                if (isset($_POST['ymd']) && !empty($_POST['ymd'])) {
                    $date1_s = $_POST['ymd'] . '-01';
                    $date1_e = date('Y-m-t', strtotime($date1_s));

                    $date2_s = date('Y-m-01', strtotime($date1_s . "+1 month"));
                    $date2_e = date('Y-m-t', strtotime($date1_s . "+1 month"));

                    $date3_s = date('Y-m-01', strtotime($date1_s . "+2 month"));
                    $date3_e = date('Y-m-t', strtotime($date1_s . "+2 month"));

                    $date4_s = date('Y-m-01', strtotime($date1_s . "+3 month"));
                    $date4_e = date('Y-m-t', strtotime($date1_s . "+3 month"));

                    $date5_s = date('Y-m-01', strtotime($date1_s . "+4 month"));
                    $date5_e = date('Y-m-t', strtotime($date1_s . "+4 month"));

                    $date6_s = date('Y-m-01', strtotime($date1_s . "+5 month"));
                    $date6_e = date('Y-m-t', strtotime($date1_s . "+5 month"));

                    if (count($order)) {
                        foreach ($order as $key => $or) {
                            $sql = "select IFNULL(sum(num*price),0) as money from app_order as ord  LEFT JOIN app_order_detail AS d ON ord.id = d.order_id WHERE status = 1 and ord.customer_id='" . $or["id"] . "' ";
                            $sql .= " AND order_at BETWEEN '" . $date1_s . "' AND '" . $date1_e . "'";
                            $order[$key]['money1'] = $this->db->query($sql)->result_array();
                            if (count($order[$key]['money1'])) {
                                foreach ($order[$key]['money1'] as $k => $or1) {
                                    $order[$key]['money1'][$k] = $or1['money'];
                                    $order[$key]['moneys'][$or1['money']] = $or1['money'];
                                }
                            }
                            $order[$key]['money1'] = array_sum($order[$key]['money1']);
                            $sql2 = "select IFNULL(sum(num*price),0) as money,ord.order_at from app_order as ord LEFT JOIN app_order_detail AS d ON ord.id = d.order_id WHERE status = 1 and ord.customer_id='" . $or["id"] . "' AND ord.order_at >'" . $date2_s . "' AND ord.order_at<='" . $date2_e . "' ";
                            $order[$key]['money2'] = $this->db->query($sql2)->result_array();
                            if (count($order[$key]['money2'])) {
                                foreach ($order[$key]['money2'] as $k2 => $or12) {
                                    $order[$key]['money2'][$k2] = $or12['money'];
                                    $order[$key]['moneys'][$or12['money']] = $or12['money'];
                                }
                            }
                            $order[$key]['money2'] = array_sum($order[$key]['money2']);

                            $sql3 = "select IFNULL(sum(num*price),0) as money,ord.order_at from app_order as ord LEFT JOIN app_order_detail AS d ON ord.id = d.order_id WHERE status = 1 and ord.customer_id='" . $or["id"] . "' AND ord.order_at >'" . $date3_s . "' AND ord.order_at<='" . $date3_e . "' ";

                            $order[$key]['money3'] = $this->db->query($sql3)->result_array();
                            if (count($order[$key]['money3'])) {
                                foreach ($order[$key]['money3'] as $k3 => $or13) {
                                    $order[$key]['money3'][$k3] = $or13['money'];
                                    $order[$key]['moneys'][$or13['money']] = $or13['money'];
                                }
                            }
                            $order[$key]['money3'] = array_sum($order[$key]['money3']);
                            $sql4 = "select IFNULL(sum(num*price),0) as money,ord.order_at from app_order as ord LEFT JOIN app_order_detail AS d ON ord.id = d.order_id WHERE status = 1 and ord.customer_id='" . $or["id"] . "' AND ord.order_at >'" . $date4_s . "' AND ord.order_at<='" . $date4_e . "' ";
                            $order[$key]['money4'] = $this->db->query($sql4)->result_array();
                            if (count($order[$key]['money4'])) {
                                foreach ($order[$key]['money4'] as $k4 => $or14) {
                                    $order[$key]['money4'][$k4] = $or14['money'];
                                    $order[$key]['moneys'][$or14['money']] = $or14['money'];
                                }
                            }
                            $order[$key]['money4'] = array_sum($order[$key]['money4']);
                            $sql5 = "select IFNULL(sum(num*price),0) as money,ord.order_at from app_order as ord LEFT JOIN app_order_detail AS d ON ord.id = d.order_id WHERE status = 1 and ord.customer_id='" . $or["id"] . "' AND ord.order_at >'" . $date5_s . "' AND ord.order_at<='" . $date5_e . "' ";
                            $order[$key]['money5'] = $this->db->query($sql5)->result_array();
                            if (count($order[$key]['money5'])) {
                                foreach ($order[$key]['money5'] as $k5 => $or15) {
                                    $order[$key]['money5'][$k5] = $or15['money'];
                                    $order[$key]['moneys'][$or15['money']] = $or15['money'];
                                }
                            }
                            $order[$key]['money5'] = array_sum($order[$key]['money5']);
                            $sql6 = "select IFNULL(sum(num*price),0) as money,ord.order_at from app_order as ord LEFT JOIN app_order_detail AS d ON ord.id = d.order_id WHERE status = 1 and ord.customer_id='" . $or["id"] . "' AND ord.order_at >'" . $date6_s . "' AND ord.order_at<='" . $date6_e . "' ";

                            $order[$key]['money6'] = $this->db->query($sql6)->result_array();
                            if (count($order[$key]['money6'])) {
                                foreach ($order[$key]['money6'] as $k6 => $or16) {
                                    $order[$key]['money6'][$k6] = $or16['money'];
                                    $order[$key]['moneys'][$or16['money']] = $or16['money'];
                                }
                            }
                            $order[$key]['money6'] = array_sum($order[$key]['money6']);
                            if (!empty($order[$key]['moneys'])) {
                                $order[$key]['moneys'] = array_sum($order[$key]['moneys']);
                            }
                        }
                    }
                }
                send_json(array('code' => -1, 'data' => $order, 'msg' => $date1));
            }

        }

        $this->load->view('information/information_purchase_user', $data);
    }

    //商品排行统计
    public function information_purchase_goods()
    {
        $customer_id = $this->session->customer_id;

        if ($this->session->customer_info['identity'] == 1) {
        } elseif ($this->session->customer_info['identity'] == 2) {
            $customer_id = $this->user_model->customer_id_to_parent_id($customer_id);;
        } elseif ($this->session->customer_info['identity'] == 3) {
            $customer_id = $this->user_model->customer_id_to_admin_id($customer_id);;
        }

        if (!empty($_GET['mydatepicker'])) {
            $mydatepicker = $_GET['mydatepicker'];
        } else {
            $mydatepicker = "";
        }

        if (!empty($_GET['mydatepicker2'])) {
            $mydatepicker2 = $_GET['mydatepicker2'];
        } else {
            $mydatepicker2 = "";
        }

        if (!empty($_GET['product_name'])) {
            $product_name = $_GET['product_name'];
        } else {
            $product_name = "";
        }

        if (isset($_GET['sort']) && !empty($_GET['sort'])) {
            $data['sort'] = $_GET['sort'];
        } else {
            $data['sort'] = 'total';
        }

        if (isset($_GET['sort_d']) && !empty($_GET['sort_d'])) {
            $data['sort_d'] = $_GET['sort_d'];
        } else {
            $data['sort_d'] = 'desc';
        }

        $data['mydatepicker2'] = $mydatepicker2;
        $data['mydatepicker'] = $mydatepicker;
        $data['product_name'] = $product_name;
        $sql = "select count(1) FROM app_cost_center AS c LEFT JOIN app_order AS o ON c.id = o.cost_center_id
           LEFT JOIN app_order_detail AS d ON o.id = d.order_id WHERE	o.status = 1 and  c.customer_id ='" . $customer_id . "' and o.order_bn is not null and d.product_id is not null";


        if ($mydatepicker) {
            $sql .= " and o.order_at >= '" . $mydatepicker . "'";
        }
        if ($mydatepicker2) {
            $sql .= " and o.order_at <= '" . $mydatepicker . "'";
        }
        if ($product_name) {
            $sql .= " and d.product_name LIKE '%$product_name%' ";
        }

        $sql .= " GROUP BY d.product_id  ";


        $data['goods'] = $this->db->query($sql)->result_array();

        $this->load->view('information/information_purchase_goods', $data);
    }

    //商品排行统计动态查询
    public function information_purchase_product()
    {
        $customer_id = $this->session->customer_id;

        if ($this->session->customer_info['identity'] == 1) {
        } elseif ($this->session->customer_info['identity'] == 2) {
            $customer_id = $this->user_model->customer_id_to_parent_id($customer_id);;
        } elseif ($this->session->customer_info['identity'] == 3) {
            $customer_id = $this->user_model->customer_id_to_admin_id($customer_id);;
        }

        //所有数据
        $sql = "SELECT
	d.product_id,
	d.product_name,
	d.sku_info,
	sku_info,
	price,
	sum(num) as num,
	sum(num*price) as total,
	order_at,
	c.customer_id
FROM
	app_cost_center AS c
LEFT JOIN app_order AS o ON c.id = o.cost_center_id
LEFT JOIN app_order_detail AS d ON o.id = d.order_id
WHERE o.status = 1 and o.order_bn is not null and d.product_id is not null and c.customer_id =" . $customer_id;
//        $data = $this->db->query($sql)->result_array();
//        send_json(array('code' => 0, 'data' => $data, 'msg' =>""));
        $dates = date('Y-m-d', time());
        $date2 = strtotime(date('Y-m', strtotime($dates)));
        $time2 = date('Y-m-d', $date2);
        if (isset($_POST['page'])) {
            $nowPage = isset($_POST['page']) ? $_POST['page'] : 1;
            if (isset($_POST['mydatepicker']) && !empty($_POST['mydatepicker'])) {
                $mydatepicker = $_POST['mydatepicker'];
                $sql .= " and o.order_at > '" . $mydatepicker . "'";
            }

            if (isset($_POST['mydatepicker2']) && !empty($_POST['mydatepicker2'])) {
                $mydatepicker2 = $_POST['mydatepicker2'];
                $sql .= " and o.order_at < '" . $mydatepicker2 . "'";
            }
            if (isset($_POST['product_name']) && !empty($_POST['product_name'])) {
                $product_name = $_POST['product_name'];
                $sql .= " and d.product_name LIKE '%$product_name%' ";
            }

            $sql .= " GROUP BY d.product_id";

            if (isset($_POST['sort']) && !empty($_POST['sort'])) {
                $dir = isset($_POST['sort_d']) && !empty($_POST['sort_d']) ? $_POST['sort_d'] : 'desc';
                $sql .= " ORDER BY {$_POST['sort']} $dir ";
            } else {
                $sql .= " ORDER BY total desc ";
            }

            if (isset($_POST['row'])) {
                $listRows = 10000000;//默认导出所有
            } else {
                $listRows = 10;
            }
            $firstRow = $listRows * ($nowPage - 1);
            $sql .= " limit " . $firstRow . "," . $listRows . "";

            $goods = $this->db->query($sql)->result_array();

            echo json_encode(array('code' => 0, 'data' => $goods, 'msg' => ""), JSON_UNESCAPED_UNICODE);

        }
    }


    //用户采购详细统计
    public function detail()
    {
        if (isset($_GET['uid']) && $_GET['uid']) {
            $customer_id = $_GET['uid'];
        } else {
            $customer_id = $this->session->customer_id;
        }

        $customer = $this->customer_model->query_customer_info($customer_id);
        if (!$customer) {
            $this->session->set_flashdata('msg', '非法操作');
            redirect('index');
        }

        if ($this->session->customer_info['identity'] == 3) {
            if ($customer_id != $this->session->customer_id) {
                $this->session->set_flashdata('msg', '无权限查看报表');
                redirect('index');
            }
        } else {
            $ids = $this->customer_model->get_customer_user($this->session->customer_id);
            if (!in_array($customer_id, $ids)) {
                $this->session->set_flashdata('msg', '无权限查看报表');
                redirect('index');
            }
        }


        $data = array();
        $data['customer'] = $customer;
        $month = date('Y-m');
        $sql = "SELECT
                        *
                        FROM app_budget as b
                        WHERE month = '" . $month . "' and customer_id =" . $customer_id;
        $budget = $this->db->query($sql)->row_array();
        $data['budget'] = $budget;
        if (isset($_GET['y']) && $_GET['y']) {
            $data['y'] = $_GET['y'];
        } else {
            $data['y'] = date('Y');
        }

        $year_begin = date('Y') . '-01-01';
        $sql = "SELECT
                        sum(money) as money,
                        count(1) as num
                        FROM app_order as o
                        WHERE o.status = 1 and o.customer_id =" . $customer_id . " and order_at >= '" . $year_begin . "'";
        $orders = $this->db->query($sql)->row_array();


        $tmp_year_begin = date('Y') . '-01-01';
        $sql = "SELECT
                    sum(budget) AS money,
                    count(1) as num
                FROM
                    app_budget AS o
                WHERE
                    budget != 0 and budget is not NULL 
                AND o.customer_id = $customer_id
                AND month >= '" . $tmp_year_begin . "' ";
        $budget_months = $this->db->query($sql)->row_array();

        if ($budget_months['num'] == 0) {
            $data['budget_month'] = 0;
        } else {
            $data['budget_month'] = $budget_months['money'] / $budget_months['num'];
        }

        $y_b = $data['y'] . '-01-01';
        $y_e = $data['y'] . '-12-31';
        $sql = "SELECT
                    DATE_FORMAT(`order_at`, '%Y-%m') AS yearMonth,
                    IFNULL(sum(num*price),0) AS money
                FROM
                    app_order AS o
                    LEFT JOIN app_order_detail AS d ON o.id = d.order_id 
                WHERE
                    o.status = 1
                AND o.customer_id = $customer_id
                AND order_at >= '" . $y_b . "' and order_at <= '" . $y_e . "'
                GROUP BY
                    yearMonth";
        $months = $this->db->query($sql)->result_array();
        $tmp = $months_sale = array();
        if (count($months)) {
            foreach ($months as $month) {
                $tmp [$month['yearMonth']] = $month['money'];
            }
        }
        for ($i = 1; $i <= 12; $i++) {
            if (!isset($tmp[$data['y'] . '-' . sprintf("%'.02d", $i)])) {
                if ($data['y'] == date('Y')) {
                    if ($i <= date('n')) {
                        $months_sale[] = '0.00';
                    }
                } else {
                    $months_sale[] = '0.00';
                }
            } else {
                $months_sale[] = $tmp[$data['y'] . '-' . sprintf("%'.02d", $i)];
            }
        }

        if ($data['y'] == date('Y')) {
            if ($orders['money'] > 0) {
                $data['actual_money'] = $orders['money'] / date('n');
            } else {
                $data['actual_money'] = 0.00;
            }
        } else {
            $sql = "SELECT
                        IFNULL(sum(num*price),0) as money,
                        count(1) as num
                        FROM app_order as o
                        LEFT JOIN app_order_detail AS d ON o.id = d.order_id 
                        WHERE o.status = 1 and o.customer_id =" . $customer_id . " 
                        AND order_at >= '" . $y_b . "' and order_at <= '" . $y_e . "'";
            $orders_y = $this->db->query($sql)->row_array();
            if ($orders_y['money'] > 0) {
                $data['actual_money'] = $orders_y['money'] / 12;
            } else {
                $data['actual_money'] = 0.00;
            }
        }

        $data['months_sale'] = json_encode($months_sale);
        $data['year_money'] = $orders['money'] ? $orders['money'] : '0.00';
        $data['year_num'] = $orders['num'] ? $orders['num'] : 0;
        $this->load->view('information/detail', $data);
    }
}